package com.salesys.gr.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.salesys.gr.util.DbUtil;
import com.salesys.gr.util.LoginedUserHelper;
import com.salesys.tony.domain.Reset;
import com.salesys.tony.domain.User;
import com.salesys.tony.domain.Withdraw;

public class UserDao {
	// 注册用户
	public int regUser(User regUser) {
		String sql = "insert into `sale_user` (`username`, `password`,`bankcard`,`nickname`,`email`,`qq`,`parent`) values (?, ?, ?, ?, ?, ?, ?)";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, regUser.getUsername());
		DbUtil.setPrepareStatementString(2, regUser.getPassword());
		DbUtil.setPrepareStatementString(3, regUser.getBankcard());
		DbUtil.setPrepareStatementString(4, regUser.getNickname());
		DbUtil.setPrepareStatementString(5, regUser.getEmail());
		DbUtil.setPrepareStatementString(6, regUser.getQq());
		DbUtil.setPrepareStatementString(7, regUser.getParent());
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 登录
	public User login(User loginUser) {
		User loginingUser = new User();
		String sql = "select `id`,`username`,`password`,`memberid`,`bankcard`,`nickname`,`email`,`qq`,`parent`,`child`,`mark` from `sale_user` where `username`=? and `password`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, loginUser.getUsername());
		DbUtil.setPrepareStatementString(2, loginUser.getPassword());
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				loginingUser.setId(rs.getInt("id"));
				loginingUser.setUsername(rs.getString("username"));
				loginingUser.setPassword(rs.getString("password"));
				loginingUser.setMemberId(rs.getString("memberid"));
				loginingUser.setBankcard(rs.getString("bankcard"));
				loginingUser.setNickname(rs.getString("nickname"));
				loginingUser.setEmail(rs.getString("email"));
				loginingUser.setQq(rs.getString("qq"));
				loginingUser.setParent(rs.getString("parent"));
				loginingUser.setChild(rs.getString("child"));
				loginingUser.setMark(rs.getString("mark"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return loginingUser;
	}

	// 删除用户
	public int deleteUser(String id) {
		String sql = "delete from `sale_user` where `id`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, id);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 从用户名和密码查询用户ID
	public int queryIdFromUsernameAndPwd(User queryUser) {
		int resultId = login(queryUser).getId();
		return resultId;
	}

	// 从用户ID查询用户名
	public User queryUsernameFromId(String id) {
		User resultUser = new User();
		String sql = "select `username`,`parent` from `sale_user` where `id`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, id);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				resultUser.setUsername(rs.getString("username"));
				resultUser.setParent("parent");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return resultUser;
	}

	// 从用户名查询下级
	public String queryChildFromUsername(String Username) {
		String child = null;
		String sql = "select `child` from `sale_user` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, Username);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				child = rs.getString("child");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return child;
	}

	// 从用户名查询资料
	public User queryInfoFromUsername(String userName) {
		User resultUser = new User();
		String sql = "select `id`,`username`,`memberid`,`bankcard`,`nickname`,`email`,`qq`,`parent`,`child`,`mark` from `sale_user` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, userName);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				resultUser.setId(rs.getInt("id"));
				resultUser.setUsername(rs.getString("username"));
				resultUser.setMemberId(rs.getString("memberid"));
				resultUser.setBankcard(rs.getString("bankcard"));
				resultUser.setNickname(rs.getString("nickname"));
				resultUser.setEmail(rs.getString("email"));
				resultUser.setQq(rs.getString("qq"));
				resultUser.setParent(rs.getString("parent"));
				resultUser.setChild(rs.getString("child"));
				resultUser.setMark(rs.getString("mark"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return resultUser;
	}

	// 从会员卡号查询资料
	public User queryInfoFromMemberId(String memberId) {
		User resultUser = new User();
		String sql = "select `id`,`username`,`memberid`,`bankcard`,`nickname`,`email`,`qq`,`parent`,`child`,`mark` from `sale_user` where `memberid`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, memberId);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				resultUser.setId(rs.getInt("id"));
				resultUser.setUsername(rs.getString("username"));
				resultUser.setMemberId(rs.getString("memberid"));
				resultUser.setBankcard(rs.getString("bankcard"));
				resultUser.setNickname(rs.getString("nickname"));
				resultUser.setEmail(rs.getString("email"));
				resultUser.setQq(rs.getString("qq"));
				resultUser.setParent(rs.getString("parent"));
				resultUser.setChild(rs.getString("child"));
				resultUser.setMark(rs.getString("mark"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return resultUser;
	}

	// 刷新用户资料
	public User freshUserInfo(User freshUser) {
		String sql = "select `id`,`username`,`password`,`memberid`,`bankcard`,`nickname`,`email`,`qq`,`parent`,`child`,`mark` from `sale_user` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, LoginedUserHelper.getLoginedUserUsername());
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				freshUser.setId(rs.getInt("id"));
				freshUser.setUsername(rs.getString("username"));
				freshUser.setPassword(rs.getString("password"));
				freshUser.setMemberId(rs.getString("memberid"));
				freshUser.setBankcard(rs.getString("bankcard"));
				freshUser.setNickname(rs.getString("nickname"));
				freshUser.setEmail(rs.getString("email"));
				freshUser.setQq(rs.getString("qq"));
				freshUser.setParent(rs.getString("parent"));
				freshUser.setChild(rs.getString("child"));
				freshUser.setMark(rs.getString("mark"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return freshUser;
	}

	// 设置下级
	public int setChild(String username, String child, String newChildId) {
		String sql = "update `sale_user` set `child`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		if (child != null) {
			DbUtil.setPrepareStatementString(1, child + newChildId + ",");
		} else {
			DbUtil.setPrepareStatementString(1, newChildId + ",");
		}
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 删除时修改下级
	public int modifyChild(String username, String child) {
		String sql = "update `sale_user` set `child`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		if (child != "") {
			DbUtil.setPrepareStatementString(1, child);
		} else {
			DbUtil.setPrepareStatementString(1, null);
		}
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 修改上级
	public int modifyParent(String id, String parentId) {
		String sql = "update `sale_user` set `parent`=? where `id`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, parentId);
		DbUtil.setPrepareStatementString(2, id);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 修改用户资料
	public int modifyInfo(User modifyInfoUser) {
		String sql = "update `sale_user` set `nickname`=?, `email`=?, `qq`=?, `bankcard`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		if (modifyInfoUser.getNickname() != "") {
			DbUtil.setPrepareStatementString(1, modifyInfoUser.getNickname());
		} else {
			DbUtil.setPrepareStatementString(1, null);
		}
		if (modifyInfoUser.getEmail() != "") {
			DbUtil.setPrepareStatementString(2, modifyInfoUser.getEmail());
		} else {
			DbUtil.setPrepareStatementString(2, null);
		}
		if (modifyInfoUser.getQq() != "") {
			DbUtil.setPrepareStatementString(3, modifyInfoUser.getQq());
		} else {
			DbUtil.setPrepareStatementString(3, null);
		}
		if (modifyInfoUser.getBankcard() != "") {
			DbUtil.setPrepareStatementString(4, modifyInfoUser.getBankcard());
		} else {
			DbUtil.setPrepareStatementString(4, null);
		}
		DbUtil.setPrepareStatementString(5, modifyInfoUser.getUsername());
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 修改会员卡号
	public int modifyMemberId(String memberId, String username) {
		String sql = "update `sale_user` set `memberid`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		if (memberId != "") {
			DbUtil.setPrepareStatementString(1, memberId);
		} else {
			DbUtil.setPrepareStatementString(1, null);
		}
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 修改密码
	public int modifyPwd(User modifyPwdUser) {
		String sql = "update `sale_user` set `password`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, modifyPwdUser.getPassword());
		DbUtil.setPrepareStatementString(2, modifyPwdUser.getUsername());
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 申请重置密码
	public int resetPSW(String username, String email) {
		String sql = "insert into `sale_pswreset` (`username`, `email`) values (?, ?)";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, username);
		DbUtil.setPrepareStatementString(2, email);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 处理重置密码申请
	public List<Reset> queryPSWReset() {
		List<Reset> resetList = new ArrayList<Reset>();
		String sql = "select * from `sale_pswreset`";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			while (rs.next()) {
				Reset reset = new Reset();
				reset.setUsername(rs.getString("username"));
				reset.setEmail(rs.getString("email"));
				resetList.add(reset);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return resetList;
	}

	// 重置密码
	public int modifyPwd(String username, String password) {
		String sql = "update `sale_user` set `password`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, password);
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 删除重置密码申请
	public int deletePSWReset(String username) {
		String sql = "delete from `sale_pswreset` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 申请提现
	public int withdrawRequest(String username, String money) {
		String sql = "insert into `sale_withdraw` (`username`, `money`) values (?, ?)";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, username);
		DbUtil.setPrepareStatementString(2, money);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 处理提现申请
	public List<Withdraw> queryWithdraw() {
		List<Withdraw> withdrawList = new ArrayList<Withdraw>();
		String sql = "select * from `sale_withdraw`";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			while (rs.next()) {
				Withdraw withdraw = new Withdraw();
				withdraw.setUsername(rs.getString("username"));
				withdraw.setMoney(rs.getString("money"));
				withdrawList.add(withdraw);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return withdrawList;
	}
	
	// 提现
	public int withdraw(String username, String mark) {
		String sql = "update `sale_user` set `mark`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, mark);
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 删除提现申请
	public int deleteWithdraw(String username) {
		String sql = "delete from `sale_withdraw` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 增加积分
	public int addMark(String mark, String username) {
		String sql = "update `sale_user` set `mark`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, mark);
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 查询登录标记
	public int isLogined(String username) {
		int isLogined = 0;
		String sql = "select `islogined` from `sale_user` where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, username);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				isLogined = Integer.parseInt(rs.getString("islogined"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return isLogined;
	}

	// 修改登录标记
	public int isLogined(String isLogined, String username) {
		String sql = "update `sale_user` set `islogined`=? where `username`=?";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.setPrepareStatementString(1, isLogined);
		DbUtil.setPrepareStatementString(2, username);
		DbUtil.updatePrepareStatement();
		int affectRow = DbUtil.getAffectRow();
		DbUtil.close();
		return affectRow;
	}

	// 检查更新
	public String checkUpdate() {
		String version = null;
		String sql = "select `version` from `sale_version` where `id`=1";
		DbUtil.getConnection();
		DbUtil.setPerpareStatement(sql);
		DbUtil.queryPrepareStatement();
		ResultSet rs = DbUtil.getResultSet();
		try {
			if (rs.next()) {
				version = rs.getString("version");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbUtil.close();
		return version;
	}
}
